const router = require('koa-router')()
const db = require('../utils/database')
// const koaMulter = require('@koa/multer')
// 文件系统   node 内置
// const fs = require('fs')
// const path = require('path')

// 前缀
router.prefix('/msbd')

router.get('/', async ctx => {
    const r = await db.doSQL('select * from classify where id > 30')
    console.log(r)
    ctx.body = 'msbd' + JSON.stringify(r)
})

// 用户列表、分页、排序、查询
router.get('/msbd-list', async ctx => {
    const { page = 0, pageSize = 20, sortBy/* 根据谁排 */, order = 'asc' /* 排列顺序 */, query } = ctx.query
    // select * from limit 0, 3  (从第几行开始查  查几个)
    let sql = `select * from msbd`
    // 查询  select * from msbd where name like '张%'   张%  张开头 %张% 包含张  张_ 一个字张开头的
    if (query) {
        sql += ` where name like '%${query}%'`
    }
    // 排序
    if (sortBy) {
        sql += ` order by ${sortBy} ${order}`
    }
    sql += ` limit ${page * pageSize},${pageSize}`
    const r = await db.doSQL(sql)
    // select count(*) as total from msbd    total  总条数
    const sqlTotal = 'select count(*) as total from msbd'
    const rTotal = await db.doSQL(sqlTotal)
    const total = rTotal[0].total
    // select * from msbd order by id(age) asc(asc小到大/desc大到小)
    ctx.body = db.doResponse({ message: '用户列表', data: r, total: total })
})

// 删除用户
router.get('/delete-msbd', async ctx => {
    const { id } = ctx.query
    const sql = 'delete from msbd where id=?'
    const r = await db.doSQL(sql, [id])
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '删除用户成功', data: r })
    } else {
        ctx.body = db.doResponse({ success: false, errorCode: 5001, message: '删除用户失败', data: r })
    }
})

// 修改用户
router.post('/update-msbd', async ctx => {
    const { title, content, author, cid, time, id } = ctx.request.body
    const sql = 'update msbd set title=?,content=?,author=?,cid=?,time=? where id=?'
    const params = [title, content, author, cid, time, id]
    const r = await db.doSQL(sql, params)
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '修改用户成功', data: r })
    } else {
        ctx.body = db.doResponse({ success: false, errorCode: 5001, message: '修改用户失败', data: r })
    }
})

// 新增用户 
router.post('/save-msbd', async ctx => {
    const { title, content, author, cid, time } = ctx.request.body
    const sql = 'insert into msbd(title, content , author,cid,time) values (?,?,?,?,?)'
    const params = [title, content, author, cid, time]
    const r = await db.doSQL(sql, params)
    console.log(r)
    if (r.affectedRows > 0) {
        ctx.body = db.doResponse({ message: '新增用户成功', data: r })
    } else {
        ctx.body = db.doResponse({ success: false, errorCode: 5001, message: '新增用户失败', data: r })
    }

})

module.exports = router